<?php if (!defined('ROOT_DIR')) exit('No direct script access!');

/**
 *@author Ervin R. Musngi <ervimusngi@gmail.com>
 *@copyright Copyright (c) 2012, Ervin Musngi
 *@DateCreated - January 16, 2012
 *@DateModified - January 16, 2012
 *
 *  #Ndb Class - Library for MySql, PosGres Driver, MSSQL, SQLLITE
 *   This contains all basic Query Building Methods
 *
 */

class Ndb {
    
    private $_connectionString;
    private $_config;
    private $_dbConnection;
    private $_select;
    private $_limit;
    private $_offset;
    private $_where_str;
    private $_order_by;
    private $_group_by;
    private $_join;
    private $_where_values  = array();
    private $_values        = array();
    private $_where         = array();
    
    public function __construct()
    {
        
        GLOBAL $system_folder;
        
        $db                 = get_config('database','db');
        $active             = get_config('database','active_group');
        $drivers            = PDO::getAvailableDrivers();
        
        /**
         * Check first if the current selected driver is installed
         */
        if ( ! in_array($db[$active]['driver'],$drivers) ) {
            require_once(ROOT_DIR.'/'.$system_folder.'/error/databaseDriverNotFound.php');
            exit();
        }
        
        $this->_config      = $db[$active];
        
        /**
         * Initialize Database Connection using PDO class
         */
        
        try
        {
            $this->_dbConnection = new PDO(
                                $db[$active]['driver'].
                                ":host=".$db[$active]['host'].
                                ";dbname=".$db[$active]['database'],
                                $db[$active]['username'],
                                $db[$active]['password']
                            );
        }
        catch(PDOException $e)
        {
            throw $e;
        }
                
    }
    
    /**
     *@method Object select() - Set columns or subquery that will be selected in the database
     *@param $column - this can be set as follows :
     *
     *                 1). String - specify which column.
     *                      ex.
     *                          $this->ndb->select('name');
     *                 2). Array - specify which column, but in array format.
     *                      ex.
     *                          $this->ndb->select(array('name','email','username'));
     *                 
     *@param $parse - check whether to parse the string or use the set format
     *@return $this - This is chainable
     */
    public function select( $column = '' , $parse = TRUE )
    {
        
        if ( !is_array($column) ) {
            
            if ( $parse === TRUE ) {
                $column = $this->parse_string( $column );
            }
            
            if ( empty($this->_select) ) {
                $this->_select = $column;
            } else {
                $this->_select .= ', ' . $column;
            }
            
        } else {
            
            foreach( $column as $col ) {
                
                if ( $parse === TRUE ) {
                    $col = $this->parse_string( $col );
                }
                
                if ( empty($this->_select) ) {
                    $this->_select = $col;
                } else {
                    $this->_select .= ', ' . $col;
                }
            }
            
        }
        
        return $this;
        
    }
    
    /**
     *@method Object join() - generate sql string with JOIN
     *@param String $table_name - the table to be join
     *@param String or Array $condition - the condition of the join
     *@param string $join - the join function to be use
     *                      1). INNER JOIN
     *                      2). LEFT JOIN
     *                      3). RIGHT JOIN
     *                      4). OUTER JOIN
     */
    
    public function join( $table_name = '' ,  $condition = '', $join = 'INNER', $parse = TRUE)
    {
        $default_join = array(
            'INNER',
            'LEFT',
            'RIGHT',
            'OUTER'
        );
        
        if ( is_array($table_name) )
        {
            $table = '';
            $alias = '';
            
            foreach( $table_name as $t => $a )
            {
                $table = $t;
                $alias = $a;
            }
            
            if ( $parse === TRUE )
            {
                $table_name = $this->parse_string($table) . ' AS ' . $this->parse_string($alias);
            }
            else
            {
                $table_name = $table . ' AS ' . $alias;
            }
        }
        
        if ( ! in_array(strtoupper($join),$default_join) )
        {
            exit('Sorry but the join : "' . $join . '"  function you specified doesn\'t exist');
        }
        
        return $this;
        
    }
    
    /**
     *@method like() - same with where except it adds LIKE operator
     *@param String $column
     *@param String $value
     *@param Boolean $parse
     */
    public function like( $column = '' , $value = '', $parse = TRUE )
    {
        return $this->where($column .' LIKE ', $value, $parse);
    }
    
    /**
     *@method like() - same with where except it adds LIKE operator and OR as separator
     *@param String $column
     *@param String $value
     *@param Boolean $parse
     */
    public function or_like( $column = '' , $value = '', $parse = TRUE )
    {
        return $this->where($column .' LIKE ', $value, $parse,'OR');
    }
    
    /**
     *@method Object or_where() - Same with where except it adds OR as condition separator
     *@param $column - This can contain :
     *
     *            1). String - Specify what column, by default the condition will be equals, you may include a condition
     *            
     *            ex.
     *                 $this->ndb->where('username','sample');
     *                 $this->ndb->where('name !=','sample');
     *                 
     *            2). Array - this will be formatted as follows :
     *            
     *             ex.
     *                 $data = array(
     *                     'username'  => 'sample',
     *                     'password'  => 'sample,
     *                     'name !='   => 'sample_name'
     *                 );
     *
     *                 $this->ndb->where($data);
     *                 
     *@param $parse - Boolean, check whether to parse the string or use the set format
     *@return  - This is chainable
     */
    
    public function or_where( $column = '' , $value = '' , $parse = TRUE )
    {
        return $this->where($column, $value, $parse, 'OR');
    }
    
    /**
     *@method Object where() - Generate the specified condition into query string
     *@param $column - This can contain :
     *
     *            1). String - Specify what column, by default the condition will be equals, you may include a condition
     *            
     *            ex.
     *                 $this->ndb->where('username','sample');
     *                 $this->ndb->where('name !=','sample');
     *                 
     *            2). Array - this will be formatted as follows :
     *            
     *             ex.
     *                 $data = array(
     *                     'username'  => 'sample',
     *                     'password'  => 'sample,
     *                     'name !='   => 'sample_name'
     *                 );
     *
     *                 $this->ndb->where($data);
     *                 
     *@param $parse - Boolean, check whether to parse the string or use the set format
     *@return  - This is chainable
     */
    
    public function where( $column = '' , $value = '', $parse = TRUE, $separator = 'AND' )
    {
        
        if ( !is_array($column) )
        {
            $no_operator = $this->_remove_operator($column);
            
            if ( $this->_has_operator($column) )
            {
                $this->_where[$no_operator] = $value;
            }
            else
            {
                $column .= ' = ';
                $this->_where[$no_operator] = $value;
            }
            
            
                    
            if ( $parse === TRUE )
            {
                $column = $this->parse_string($column);
            }
            
            
            if ( strlen($this->_where_str) == 0 )
            {
                if ( strlen($value) > 0 )
                {
                   $this->_where_values[':'.$no_operator] = $value;
                    $this->_where_str = $column .' :'.$no_operator;
                }
                else
                {
                    $this->_where_str = $column;
                }
            }
            else
            {
                if ( strlen($value) > 0 )
                {
                    $this->_where_values[':'.$no_operator] = $value;
                    $this->_where_str .= ' ' . $separator . ' ' . $column . ' :' . $no_operator;
                }
                else
                {
                    $this->_where_str .=  ' ' . $separator . ' ' . $column;
                }
            }
            
        }
        else
        {
            
            foreach( $column as $col => $val )
            {
                
                $no_operator = $this->_remove_operator($col);
            
                if ( $this->_has_operator($col) )
                {
                    $this->_where[$no_operator] = $val;
                }
                else
                {
                    $col .= ' = ';
                    $this->_where[$no_operator] = $val;
                }
                
                
                if ( $parse === TRUE )
                {
                    $col = $this->parse_string($col);
                }
                
                if ( strlen($this->_where_str) == 0 )
                {
                    if ( strlen($val) > 0 )
                    {
                        $this->_where_values[':'.$no_operator] = $val;
                        $this->_where_str = $col .' :'.$no_operator;
                    }
                    else
                    {
                        $this->_where_str = $col;
                    }
                }
                else
                {
                    if ( strlen($val) > 0 )
                    {
                        $this->_where_values[':'.$no_operator] = $val;
                        $this->_where_str .= ' ' . $separator . ' ' . $col . ' :' . $no_operator;
                    }
                    else
                    {
                        $this->_where_str .= ' ' . $separator . ' ' . $col;
                    }
                }
                
            }
            
        }
        
        return $this;
    }
    
    public function limit($limit = '', $offset = '')
    {
        $this->_limit = $limit;
            
        $this->_offset = $offset;
        
        return $this;
    }
    
    /**
     *@method Object get() - return the result set for the current query
     *@param String $table_name - the current table name, don't include prefix if you have already specify the prefix inide the database config file
     *@param Int $limit - the current limit of number of result should be return
     *@param Int $offset - where should the result start
     */
    
    public function get( $table_name = '', $table_alias = '', $parse = TRUE )
    {
        try
        {
            if( strlen($table_alias) > 0 )
            {
                if ( $parse === TRUE )
                {
                    $table_name = $this->parse_string($this->_config['prefix'].$table_name) . ' AS ' . $this->parse_string($table_alias);
                }
                else
                {
                    $table_name = $this->_config['prefix'].$table_name . ' AS ' . $table_alias;
                }
            }
            
            $sql = $this->_generate_sql_string( 'select' , $table_name );
            
            $prepared = $this->_dbConnection->prepare($sql,array(PDO::ATTR_CURSOR => PDO::CURSOR_FWDONLY));
            
            $prepared->execute($this->_where_values);
            
            return $prepared;
        }
        catch(PDOException $e)
        {
            throw $e;
        }
    }
    
    /**
     *@method order_by() - add ORDER BY to query
     *@param String $column - which column should be order by
     *@param String $direction  - which direction to be order
     *                          1). ASC - Ascending
     *                          2). DESC - Descending
     *@param $parse - Check whether to parse the string
     */
    
    public function order_by( $column = '' , $direction = "ASC" , $parse = TRUE)
    {
        if ( $parse === TRUE)
        {
            $column = $this->parse_string($column);
        }
        
        $this->_order_by = ' ORDER BY ' . $column. ' ' . $direction;
        
        return $this;
    }
    
    /**
     *@method group_by() - add GROUP BY to query
     *@param String $column - which column should be group by
     *@param $parse - Check whether to parse the string
     */
    
    public function group_by( $column = '', $parse = TRUE)
    {
        if ( $parse === TRUE)
        {
            $column = $this->parse_string($column);
        }
        
        $this->_group_by = ' GROUP BY ' . $column;
        
        return $this;
    }
    
    /**
     *@method String generate_sql_string() - generate the specified query string
     *@param string $type - what type of query should be generated
     *                      1). select
     *                      2). insert
     *                      3). update
     *                      4). delete
     *                      
     *@param string $table - specify table
     */
    
    private function _generate_sql_string( $type = 'select', $table = '' )
    {
        $sql = '';
        
        switch($type)
        {
            case 'select' :
                
                if ( empty($this->_select) )
                {
                    $this->_select = ' * ';
                }
                
                $sql = ' SELECT ' . $this->_select . ' FROM ' . $table;
                
                if ( strlen($this->_where_str) > 0 )
                {
                    $sql .= ' WHERE ' . $this->_where_str;
                }
                
                if ( strlen($this->_group_by) > 0 )
                {
                    $sql .= ' ' . $this->_group_by;
                }
                
                if ( strlen($this->_order_by) > 0 )
                {
                    $sql .= ' ' . $this->_order_by;
                }
                
                if ( strlen($this->_limit) > 0 || $this->_limit > 0 )
                {
                    $sql .= ' LIMIT ' . $this->_limit;
                }
                
                if ( strlen($this->_offset) > 0 || $this->_offset > 0 )
                {
                    $sql .= ' ' . $this->_offset;
                }

                return $sql;
                
                break;
            case 'insert' :
                break;
            case 'update' :
                break;
            case 'delete' :
                break;
            default :
                exit('NO TYPE SELECTED');
                break;
        }
        
        return $sql;
        
    }
    
    /**
     *@method Boolean _has_operator() - Check if the current string has an existing operator :
     *          1). = OR !=
     *          3). > OR <
     *          4). => OR =<
     *          5). is null or is not null
     *          6). like or not like
     *@param String $string - String to be check
     */
    
    private function _has_operator( $string = '' )
    {
        $string = trim($string);
        
        if ( ! preg_match("/(\s|<|>|!|=|is null|is not null|like|not like)/i", $string))
        {
            return FALSE;
        }
        
        return TRUE;
    }
    
    private function _remove_operator( $string = '' )
    {
        $operator = array('=','!','<','>','is null','is not null','like','not like',' ');
        $operator =  str_replace($operator,"",$string);
        $data = explode('.',$operator);
        
        if ( count($data) > 1 )
        {
            return implode("",$data);
        }
        else
        {
            return $data[0];
        }
    }
    
    /**
     *@method String parse_string() - this will use to parse and escape strings
     *@param $string - the current string to be parse or escape
     */
    
    public function parse_string( $string = '' )
    {
        
        $data = explode('.',$string);
        
        if ( count($data) > 1 )
        {
            $ope = $this->_remove_operator($data[1]);
            
            $col = str_replace($ope,'`'.$ope.'`',$data[1]);
            
            return '`'.$data[0].'`.'.$col;
        }
        else
        {
            $ope = $this->_remove_operator($string);
            
            $col = str_replace($ope,'`'.$ope.'`',$string);
            
            return $col;
        }
    }
    
    /**
     *@method String get_sql_string() - this will return the formatted SQL query string
     */
    public function get_sql_string()
    {
        return $this->_where_values;
    }
    
}